from flask_sqlalchemy import SQLAlchemy
from datetime import datetime
import json

db = SQLAlchemy()

class User(db.Model):
    __tablename__ = 'users'
    
    id = db.Column(db.Integer, primary_key=True)
    wx_openid = db.Column(db.String(100), unique=True, nullable=False)
    role = db.Column(db.String(20), nullable=False, default='student')  # student/parent
    nickname = db.Column(db.String(100), nullable=False)
    avatar = db.Column(db.String(200))
    created_at = db.Column(db.DateTime, default=datetime.utcnow)
    
    # Relationships
    sessions = db.relationship('Session', backref='user', lazy=True)
    practice_logs = db.relationship('PracticeLog', backref='user', lazy=True)
    mistakes = db.relationship('Mistake', backref='user', lazy=True)
    weekly_tests = db.relationship('WeeklyTest', backref='user', lazy=True)
    weekly_reports = db.relationship('WeeklyReport', backref='user', lazy=True)
    parent_feedbacks = db.relationship('ParentFeedback', backref='user', lazy=True)

    def to_dict(self):
        return {
            'id': self.id,
            'wx_openid': self.wx_openid,
            'role': self.role,
            'nickname': self.nickname,
            'avatar': self.avatar,
            'created_at': self.created_at.isoformat() if self.created_at else None
        }

class WordSet(db.Model):
    __tablename__ = 'word_sets'
    
    id = db.Column(db.Integer, primary_key=True)
    grade = db.Column(db.String(20), nullable=False)
    title = db.Column(db.String(100), nullable=False)
    source = db.Column(db.String(100))
    size = db.Column(db.Integer, default=0)
    
    # Relationships
    words = db.relationship('Word', backref='word_set', lazy=True)

    def to_dict(self):
        return {
            'id': self.id,
            'grade': self.grade,
            'title': self.title,
            'source': self.source,
            'size': self.size
        }

class Word(db.Model):
    __tablename__ = 'words'
    
    id = db.Column(db.Integer, primary_key=True)
    set_id = db.Column(db.Integer, db.ForeignKey('word_sets.id'), nullable=False)
    en = db.Column(db.String(100), nullable=False)
    zh = db.Column(db.String(200), nullable=False)
    phonetic = db.Column(db.String(200))
    image_url = db.Column(db.String(500))
    audio_url = db.Column(db.String(500))
    tags = db.Column(db.Text)  # JSON string
    
    # Relationships
    examples = db.relationship('Example', backref='word', lazy=True)
    practice_logs = db.relationship('PracticeLog', backref='word', lazy=True)
    mistakes = db.relationship('Mistake', backref='word', lazy=True)

    def to_dict(self):
        return {
            'id': self.id,
            'set_id': self.set_id,
            'en': self.en,
            'zh': self.zh,
            'phonetic': self.phonetic,
            'image_url': self.image_url,
            'audio_url': self.audio_url,
            'tags': json.loads(self.tags) if self.tags else []
        }

class Example(db.Model):
    __tablename__ = 'examples'
    
    id = db.Column(db.Integer, primary_key=True)
    word_id = db.Column(db.Integer, db.ForeignKey('words.id'), nullable=False)
    en_sentence = db.Column(db.Text, nullable=False)
    zh_sentence = db.Column(db.Text, nullable=False)
    audio_url = db.Column(db.String(500))

    def to_dict(self):
        return {
            'id': self.id,
            'word_id': self.word_id,
            'en_sentence': self.en_sentence,
            'zh_sentence': self.zh_sentence,
            'audio_url': self.audio_url
        }

class Session(db.Model):
    __tablename__ = 'sessions'
    
    id = db.Column(db.Integer, primary_key=True)
    user_id = db.Column(db.Integer, db.ForeignKey('users.id'), nullable=False)
    date = db.Column(db.Date, nullable=False)
    plan_count = db.Column(db.Integer, default=10)
    done_count = db.Column(db.Integer, default=0)
    duration_sec = db.Column(db.Integer, default=0)
    created_at = db.Column(db.DateTime, default=datetime.utcnow)

    def to_dict(self):
        return {
            'id': self.id,
            'user_id': self.user_id,
            'date': self.date.isoformat() if self.date else None,
            'plan_count': self.plan_count,
            'done_count': self.done_count,
            'duration_sec': self.duration_sec,
            'created_at': self.created_at.isoformat() if self.created_at else None
        }

class PracticeLog(db.Model):
    __tablename__ = 'practice_logs'
    
    id = db.Column(db.Integer, primary_key=True)
    user_id = db.Column(db.Integer, db.ForeignKey('users.id'), nullable=False)
    word_id = db.Column(db.Integer, db.ForeignKey('words.id'), nullable=False)
    timestamp = db.Column(db.DateTime, default=datetime.utcnow)
    action = db.Column(db.String(50), nullable=False)  # view/tts/record/score
    score = db.Column(db.Float)
    correct = db.Column(db.Boolean)

    def to_dict(self):
        return {
            'id': self.id,
            'user_id': self.user_id,
            'word_id': self.word_id,
            'timestamp': self.timestamp.isoformat() if self.timestamp else None,
            'action': self.action,
            'score': self.score,
            'correct': self.correct
        }

class Mistake(db.Model):
    __tablename__ = 'mistakes'
    
    id = db.Column(db.Integer, primary_key=True)
    user_id = db.Column(db.Integer, db.ForeignKey('users.id'), nullable=False)
    word_id = db.Column(db.Integer, db.ForeignKey('words.id'), nullable=False)
    last_timestamp = db.Column(db.DateTime, default=datetime.utcnow)
    count = db.Column(db.Integer, default=1)
    mastery_level = db.Column(db.Integer, default=20)  # 0-100

    def to_dict(self):
        return {
            'id': self.id,
            'user_id': self.user_id,
            'word_id': self.word_id,
            'last_timestamp': self.last_timestamp.isoformat() if self.last_timestamp else None,
            'count': self.count,
            'mastery_level': self.mastery_level
        }

class WeeklyTest(db.Model):
    __tablename__ = 'weekly_tests'
    
    id = db.Column(db.Integer, primary_key=True)
    user_id = db.Column(db.Integer, db.ForeignKey('users.id'), nullable=False)
    week_index = db.Column(db.Integer, nullable=False)
    questions = db.Column(db.Text)  # JSON string
    score = db.Column(db.Float)
    report_json = db.Column(db.Text)  # JSON string
    created_at = db.Column(db.DateTime, default=datetime.utcnow)

    def to_dict(self):
        return {
            'id': self.id,
            'user_id': self.user_id,
            'week_index': self.week_index,
            'questions': json.loads(self.questions) if self.questions else [],
            'score': self.score,
            'report_json': json.loads(self.report_json) if self.report_json else {},
            'created_at': self.created_at.isoformat() if self.created_at else None
        }

class WeeklyReport(db.Model):
    __tablename__ = 'weekly_reports'
    
    id = db.Column(db.Integer, primary_key=True)
    user_id = db.Column(db.Integer, db.ForeignKey('users.id'), nullable=False)
    week_index = db.Column(db.Integer, nullable=False)
    kpis_json = db.Column(db.Text)  # JSON string with KPIs
    praise_text = db.Column(db.Text)
    created_at = db.Column(db.DateTime, default=datetime.utcnow)

    def to_dict(self):
        return {
            'id': self.id,
            'user_id': self.user_id,
            'week_index': self.week_index,
            'kpis_json': json.loads(self.kpis_json) if self.kpis_json else {},
            'praise_text': self.praise_text,
            'created_at': self.created_at.isoformat() if self.created_at else None
        }

class ParentFeedback(db.Model):
    __tablename__ = 'parent_feedbacks'
    
    id = db.Column(db.Integer, primary_key=True)
    user_id = db.Column(db.Integer, db.ForeignKey('users.id'), nullable=False)
    week_index = db.Column(db.Integer, nullable=False)
    liked = db.Column(db.Boolean, default=False)
    reward_text = db.Column(db.String(200))
    created_at = db.Column(db.DateTime, default=datetime.utcnow)

    def to_dict(self):
        return {
            'id': self.id,
            'user_id': self.user_id,
            'week_index': self.week_index,
            'liked': self.liked,
            'reward_text': self.reward_text,
            'created_at': self.created_at.isoformat() if self.created_at else None
        }

